using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Model;
using OgilvyOne.IDAL;

namespace OgilvyOne.SqlServerDAL
{
    public class SXjpeCard : IXjpeCard
    {
        // Methods
        public bool DeleteByIdentity(int id)
        {
            bool flag = false;
            int num = 0;
            string cmdText = "";
            cmdText = "DELETE FROM xjpeCard WHERE id=@id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 10) };
            cmdParms[0].Value = id;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                        if (num > 0)
                        {
                            flag = true;
                        }
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return flag;
                }
            }
        }

        public MXjpeCard GetRowByIdentity(int id)
        {
            MXjpeCard card = null;
            string cmdText = "";
            cmdText = "SELECT id,zhufu,myname,myemail,mfemail1,mfemail2,mfemail3,mfemail4,mfemail5,chaosong,maction,isrdt,state FROM xjpeCard WHERE id=@id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 10) };
            cmdParms[0].Value = id;
            using (SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, cmdText, cmdParms))
            {
                while (reader.Read())
                {
                    try
                    {
                        card = new MXjpeCard();
                        card.Id = Convert.ToInt32(reader[0]);
                        card.Zhufu = Convert.ToString(reader[1]);
                        card.Myname = Convert.ToString(reader[2]);
                        card.Myemail = Convert.ToString(reader[3]);
                        card.Mfemail1 = Convert.ToString(reader[4]);
                        card.Mfemail2 = Convert.ToString(reader[5]);
                        card.Mfemail3 = Convert.ToString(reader[6]);
                        card.Mfemail4 = Convert.ToString(reader[7]);
                        card.Mfemail5 = Convert.ToString(reader[8]);
                        card.Chaosong = Convert.ToString(reader[9]);
                        card.Maction = Convert.ToString(reader[10]);
                        card.Isrdt = Convert.ToString(reader[11]);
                        card.State = Convert.ToInt32(reader[12]);
                    }
                    catch (SqlException exception)
                    {
                        Console.WriteLine(exception.Message);
                    }
                }
            }
            return card;
        }

        public DataSet GetRows(string condition)
        {
            DataSet dataSet = new DataSet();
            string str = "SELECT id,zhufu,myname,myemail,mfemail1,mfemail2,mfemail3,mfemail4,mfemail5,chaosong,maction,isrdt,state FROM xjpeCard " + condition;
            using (SqlDataAdapter adapter = new SqlDataAdapter())
            {
                try
                {
                    adapter.SelectCommand = new SqlCommand();
                    adapter.SelectCommand.CommandText = str;
                    adapter.SelectCommand.Connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC);
                    adapter.Fill(dataSet);
                }
                catch (SqlException exception)
                {
                    Console.WriteLine(exception.Message);
                }
            }
            return dataSet;
        }

        public int Insert(MXjpeCard data)
        {
            int num = 0;
            string cmdText = "INSERT INTO xjpeCard(zhufu,myname,myemail,mfemail1,mfemail2,mfemail3,mfemail4,mfemail5,chaosong,maction,state) VALUES(@zhufu, @myname, @myemail, @mfemail1, @mfemail2, @mfemail3, @mfemail4, @mfemail5, @chaosong, @maction, @state)";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@zhufu", SqlDbType.NVarChar, 500), new SqlParameter("@myname", SqlDbType.NVarChar, 50), new SqlParameter("@myemail", SqlDbType.NVarChar, 200), new SqlParameter("@mfemail1", SqlDbType.NVarChar, 200), new SqlParameter("@mfemail2", SqlDbType.NVarChar, 200), new SqlParameter("@mfemail3", SqlDbType.NVarChar, 200), new SqlParameter("@mfemail4", SqlDbType.NVarChar, 200), new SqlParameter("@mfemail5", SqlDbType.NVarChar, 200), new SqlParameter("@chaosong", SqlDbType.NVarChar, 50), new SqlParameter("@maction", SqlDbType.NVarChar, 50), new SqlParameter("@state", SqlDbType.Int, 10) };
            cmdParms[0].Value = data.Zhufu;
            cmdParms[1].Value = data.Myname;
            cmdParms[2].Value = data.Myemail;
            cmdParms[3].Value = data.Mfemail1;
            cmdParms[4].Value = data.Mfemail2;
            cmdParms[5].Value = data.Mfemail3;
            cmdParms[6].Value = data.Mfemail4;
            cmdParms[7].Value = data.Mfemail5;
            cmdParms[8].Value = data.Chaosong;
            cmdParms[9].Value = data.Maction;
            cmdParms[10].Value = data.State;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return num;
                }
            }
        }

        public bool ModifyByIdentity(MXjpeCard data)
        {
            bool flag = false;
            int num = 0;
            string cmdText = "";
            cmdText = "UPDATE xjpeCard SET zhufu=@zhufu,myname=@myname,myemail=@myemail,mfemail1=@mfemail1,mfemail2=@mfemail2,mfemail3=@mfemail3,mfemail4=@mfemail4,mfemail5=@mfemail5,chaosong=@chaosong,maction=@maction,state=@state WHERE id=@id";
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 10), new SqlParameter("@zhufu", SqlDbType.NVarChar, 500), new SqlParameter("@myname", SqlDbType.NVarChar, 50), new SqlParameter("@myemail", SqlDbType.NVarChar, 200), new SqlParameter("@mfemail1", SqlDbType.NVarChar, 200), new SqlParameter("@mfemail2", SqlDbType.NVarChar, 200), new SqlParameter("@mfemail3", SqlDbType.NVarChar, 200), new SqlParameter("@mfemail4", SqlDbType.NVarChar, 200), new SqlParameter("@mfemail5", SqlDbType.NVarChar, 200), new SqlParameter("@chaosong", SqlDbType.NVarChar, 50), new SqlParameter("@maction", SqlDbType.NVarChar, 50), new SqlParameter("@state", SqlDbType.Int, 10) };
            cmdParms[0].Value = data.Id;
            cmdParms[1].Value = data.Zhufu;
            cmdParms[2].Value = data.Myname;
            cmdParms[3].Value = data.Myemail;
            cmdParms[4].Value = data.Mfemail1;
            cmdParms[5].Value = data.Mfemail2;
            cmdParms[6].Value = data.Mfemail3;
            cmdParms[7].Value = data.Mfemail4;
            cmdParms[8].Value = data.Mfemail5;
            cmdParms[9].Value = data.Chaosong;
            cmdParms[10].Value = data.Maction;
            cmdParms[11].Value = data.State;
            using (SqlConnection connection = new SqlConnection(SQLHelper.CONN_STRING_NON_DTC))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        num = SQLHelper.ExecuteNonQuery(transaction, CommandType.Text, cmdText, cmdParms);
                        transaction.Commit();
                        if (num > 0)
                        {
                            flag = true;
                        }
                    }
                    catch (SqlException exception)
                    {
                        transaction.Rollback();
                        Console.WriteLine(exception.Message);
                    }
                    return flag;
                }
            }
        }
    }
}